package employee;

import DBCP.MyDBCP;
import employee.Employee;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

@WebServlet("/employee")
public class EmployeeServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String action = req.getParameter("action");
        if (action.equals("SignOut")){
            //退出登录
             signOut(req,resp);
        }
        if (action.equals("Remove")){
            //管理员删除员工
             remove(req,resp);
        }
        if (action.equals("Editer")){
            req.getSession().setAttribute("role",1);
             findEmp(req,resp,"editer.jsp");
        }
        if (action.equals("Detail")){
            findEmp(req,resp,"informationEmployee.jsp");
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String action = req.getParameter("action");
        if (action.equals("SignUp")){
            req.getSession().invalidate();
            addEmployee(req,resp,"index.jsp","SignUp.jsp");
        }
        if (action.equals("SignIn")){
            req.getSession().invalidate();
            signIn(req,resp);
        }
        if (action.equals("addEmployee")){
            req.getSession().setAttribute("role",1);
            addEmployee(req,resp,"admin.jsp","addEmployee.jsp");
            selectAll(req,resp);
        }
        if (action.equals("Update")){
            req.getSession().setAttribute("role",1);
            update(req,resp);
            selectAll(req,resp);
        }
        if (action.equals("Search")){
            search(req,resp,"informationEmployee.jsp","admin.jsp");
        }
    }
    //添加用户
    private static void addEmployee(HttpServletRequest req, HttpServletResponse resp,String successfulPath,String errorPath) throws ServletException, IOException {
          String name = req.getParameter("name");
          String password = req.getParameter("password");
          int age = Integer.parseInt(req.getParameter("age"));
          String gender = req.getParameter("gender");

          String insertSql = "insert into db_employee.employee values (null,?,?,?,?,0)";
          Connection con = MyDBCP.getConnection();
          PreparedStatement ps_id = null;
          PreparedStatement psInsert = null;
          ResultSet resultSet = null;
        try {
                psInsert = con.prepareStatement(insertSql);
                psInsert.setString(1,name);
                psInsert.setString(2,password);
                psInsert.setInt(3,age);
                psInsert.setString(4,gender);
                psInsert.executeUpdate();

                ps_id = con.prepareStatement("select last_insert_id()");
                resultSet = ps_id.executeQuery();
                resultSet.next();
                int id = resultSet.getInt(1);
                req.getSession().setAttribute("message","successful insert  your Id is:"+id);
                resp.sendRedirect(successfulPath);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            MyDBCP.close(null,psInsert,con);
            MyDBCP.close(resultSet,ps_id,con);
        }

    }
    //登录
    private static void signIn(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException {

        int id  = Integer.parseInt(req.getParameter("id"));
        String password = req.getParameter("password");
        String selectSql = "select * from db_employee.employee where id=? and password = ?";
        Connection con = MyDBCP.getConnection();
        PreparedStatement ps =  null;
        ResultSet rs_select = null;
        try {
            ps = con.prepareStatement(selectSql);
            ps.setInt(1,id);
            ps.setString(2,password);
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.next()){
                int role = resultSet.getInt("role");
                if (role == 0){
                    int emp_id  = resultSet.getInt("id");
                    String emp_name  = resultSet.getString("name");
                    String emp_password  = resultSet.getString("password");
                    int emp_age  = resultSet.getInt("age");
                    String emp_gender  = resultSet.getString("gender");
                    Employee employee = new Employee(emp_id,emp_name,emp_password,emp_age,emp_gender);
                    req.getSession().setAttribute("employee",employee);
                    req.getSession().setAttribute("name",emp_name);
                    req.getSession().setAttribute("role",role);
                    resp.sendRedirect("user.jsp");
                }
                if (role == 1){
                    selectAll(req,resp);
                    req.getSession().setAttribute("name",resultSet.getString("name"));
                    req.getSession().setAttribute("role",role);
                    resp.sendRedirect("admin.jsp");
                }
            }else {
                req.getSession().setAttribute("error","check your id and password!");
                resp.sendRedirect("index.jsp");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    //查询全体
    private static void selectAll(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException {
        String selectAllSql = "select * from db_employee.employee where role = 0";
        Connection con  = MyDBCP.getConnection();
        PreparedStatement ps_selectAll = null;
        ResultSet resultSet = null;
        try {
            ps_selectAll = con.prepareStatement(selectAllSql);
            resultSet = ps_selectAll.executeQuery();
            ArrayList<Employee> employees = new ArrayList<Employee>();
            while (resultSet.next()){
                int employee_id = resultSet.getInt("id");
                String employee_name = resultSet.getString("name");
                int employee_age = resultSet.getInt("age");
                String employee_gender = resultSet.getString("gender");
                Employee employee = new Employee(employee_id,employee_name,employee_age,employee_gender);
                employees.add(employee);
            }
            req.getSession().setAttribute("employees",employees);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            MyDBCP.close(resultSet,ps_selectAll,con);
        }

    }
    //退出登录
    private static void signOut(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException{
        req.getSession().invalidate();
        resp.sendRedirect("index.jsp");
    }
    //删除
    private static void remove(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException{
        int id = Integer.parseInt(req.getParameter("id"));
        String delectSql = "delete  from db_employee.employee where id=?";
        Connection con = MyDBCP.getConnection();
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(delectSql);
            ps.setInt(1,id);
            ps.executeUpdate();
            selectAll(req,resp);
            resp.sendRedirect("admin.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            MyDBCP.close(null,ps,con);
        }
    }
    //编辑
    private static void findEmp(HttpServletRequest req, HttpServletResponse resp,String aimPatch)throws ServletException, IOException{
           int id = Integer.parseInt(req.getParameter("id"));
           Connection con  = MyDBCP.getConnection();
           String selectSql = "select  * from db_employee.employee where id = ?";
           PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(selectSql);
            ps.setInt(1,id);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()){
               String name  = resultSet.getString("name");
               String password  = resultSet.getString("password");
               int age = resultSet.getInt("age");
               String gender = resultSet.getString("gender");
               Employee employee = new Employee(id,name,password,age,gender);
               req.getSession().setAttribute("employee",employee);
               resp.sendRedirect(aimPatch);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            MyDBCP.close(null,ps,con);
        }

    }
    //更新修改
    private static void update(HttpServletRequest req, HttpServletResponse resp)throws ServletException, IOException{
             int id = Integer.parseInt(req.getParameter("id"));
             String name = req.getParameter("name");
             String password = req.getParameter("password");
             int age = Integer.parseInt(req.getParameter("age"));
             String gender = req.getParameter("gender");

             String updateSql = "update db_employee.employee set name = ? , password = ? ,age = ? ,gender = ? where id = ?";

             Connection con = MyDBCP.getConnection();
             PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(updateSql);
            ps.setString(1,name);
            ps.setString(2,password);
            ps.setInt(3,age);
            ps.setString(4,gender);
            ps.setInt(5,id);
            ps.executeUpdate();
            req.getSession().setAttribute("message","Complish Update!");
            resp.sendRedirect("admin.jsp");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
           MyDBCP.close(null,ps,con);
        }
    }
    //查询员工
    private static void search(HttpServletRequest req, HttpServletResponse resp,String successfulPath,String errorPath)throws ServletException, IOException{
                String name = req.getParameter("search");
                String selecNameSql = "select * from db_employee.employee where name = ? ";
                Connection con = MyDBCP.getConnection();
                PreparedStatement ps_name = null;
        try {
            ps_name = con.prepareStatement(selecNameSql);
            ps_name.setString(1,name);
            ResultSet resultSet = ps_name.executeQuery();
            if (resultSet.next()){
                int id = resultSet.getInt("id");
                String password  = resultSet.getString("password");
                int age = resultSet.getInt("age");
                String gender = resultSet.getString("gender");
                Employee employee = new Employee(id,name,password,age,gender);
                req.getSession().setAttribute("employee",employee);
                resp.sendRedirect(successfulPath);
            }else {
                req.getSession().setAttribute("message","name not exist!");
                resp.sendRedirect(errorPath);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
           MyDBCP.close(null,ps_name,con);

        }
    }
}
